﻿Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Security.Cryptography
Imports System.Text
Imports System.Xml

''' <summary>
''' Shared Class for MDX Test oprations
''' </summary>
''' <remarks></remarks>
Friend Class MdxHelper

    Public Shared currentNLB_Node As Integer = 0
    Public Shared parameterTable As DataTable   '' Optional parameter, used to replace parameters with new default values and loop through different dates, etc
    Public Shared parameterValues As New Dictionary(Of String, List(Of Object))    '' Possible Values for a Parameter
    Public Shared errLastMdxQuery As String
    Public Shared errLastParameters As String
    Dim cnAs As New AdomdConnection


    Public Shared ReadOnly Property CubeName As String
        Get
            Return My.Settings.mdDatabaseName
        End Get
    End Property


    Public Sub Connect()
        cnAs.ConnectionString = CubeCS
        cnAs.Open()
    End Sub

    Public Sub DisConnect()
        If cnAs.State = ConnectionState.Open Then
            cnAs.Close()
            cnAs.Dispose()
        End If
    End Sub

    ''' <summary>
    ''' Return Cube Connection string. Accounting for NLB Simulation (if used)
    ''' </summary>
    ''' <value></value>
    ''' <returns>Name of SSAS Server to open connection on</returns>
    ''' <remarks></remarks>
    Public Shared ReadOnly Property CubeCS As String
        Get
            Dim sServer As String = My.Settings.mdDatabaseName

            Return String.Format("Data Source={0};Catalog={1}", My.Settings.mdServerName, My.Settings.mdDatabaseName)
        End Get
    End Property

    ''' <summary>
    ''' Clear the SSAS Cache. Note we do not clear the FleSystem Cache
    '''  use "call ASSP.ClearFileSystemCache()" after installing Analysis Services Storted procedure Project
    ''' http://asstoredprocedures.codeplex.com/wikipage?title=FileSystemCache
    ''' </summary>
    ''' <remarks>Its important to cleat the FileSystem Cache if we need pure "cold" tests</remarks>
    Public Sub ClearCache()

        Dim mdx As String = "call ASSP.ClearAllCaches()"    'Clear FileSystem and Data Cache

        'Dim mdx As String = String.Format("<ClearCache xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Object><DatabaseID>{0}</DatabaseID></Object></ClearCache>", My.Settings.DatabaseID)

        ExecuteXMLA(mdx)

        'dummy query to force script recompilation
        mdx = String.Format("select {0} on COLUMNS from [{1}]", "{}", My.Settings.mdCubeName)
        ExecuteMDX(mdx)
    End Sub


    ''' <summary>
    ''' Clean MDX by adding CRLF into the statements. This may help resolve issues with comments in statements
    ''' See http://blogs.prodata.ie/post/MDX-Best-Practices-e28093-Careful-how-you-comment-queries.aspx
    ''' </summary>
    ''' <param name="sMDX"></param>
    ''' <remarks></remarks>
    Public Sub cleanMDX(ByRef sMDX As String)
        sMDX = Replace(sMDX, " MEMBER ", Chr(13) + Chr(10) + " MEMBER ")
        sMDX = Replace(sMDX, ",iif", Chr(13) + Chr(10) + ",iif")
        sMDX = Replace(sMDX, " {", Chr(13) + Chr(10) + "{")
        sMDX = Replace(sMDX, " FILTER", Chr(13) + Chr(10) + " FILTER")
        sMDX = Replace(sMDX, " [Measures]", Chr(13) + Chr(10) + "[Measures]")
        sMDX = Replace(sMDX, " Descendants", Chr(13) + Chr(10) + " Descendants")
        sMDX = Replace(sMDX, "}", Chr(13) + Chr(10) + "}")
    End Sub

    ''' <summary>
    ''' Parse MDX String for Parameters and add parameters to OLEDB command object
    ''' </summary>
    ''' <param name="sMDX"></param>
    ''' <param name="cmd">ADOMD Command object for target parameters</param>
    ''' <remarks></remarks>
    Public Sub parseParams(ByRef sMDX As String, ByRef cmd As AdomdCommand)
        Dim mc As RegularExpressions.Match
        Dim hParams As New Hashtable
        Dim name As String
        Dim r As New RegularExpressions.Regex("@\w+")

        For Each mc In r.Matches(sMDX)
            name = Replace(mc.ToString, "@", "")
        Next
    End Sub


    ''' <summary>
    ''' Parse Paramers Contained in XML requestParamers from an SSAS Trace File
    ''' </summary>
    ''' <param name="sMDX">MDX Query with Parameters</param>
    ''' <param name="cmd">ADOMD Command object for target parameters</param>
    ''' <param name="requestParameters">XML Parameter String from Tarce File</param>
    ''' <remarks></remarks>
    Public Sub parseParams(ByRef sMDX As String, ByRef cmd As AdomdCommand, ByVal requestParameters As String)
        Dim xml_doc As New Xml.XmlDocument
        Dim name As String
        Dim value As String
        Dim replaceParams As Boolean = My.Settings.ReplaceParams

        xml_doc.LoadXml(requestParameters)

        Dim child_nodes As XmlNodeList = xml_doc.GetElementsByTagName("Parameter")

        For Each c As XmlNode In child_nodes
            name = c.ChildNodes(0).InnerText
            value = c.ChildNodes(1).InnerText

            If Not cmd.Parameters.Contains(name) Then
                If value = "default" Or value = "random" Then replaceParams = True
                cmd.Parameters.Add(New AdomdParameter(name, value))
            End If

        Next

        If replaceParams Then Call replaceDefaultParams(cmd)
    End Sub

    ''' <summary>
    ''' Execute MDX and return a checksum and cellcount
    ''' Thsi can be used to verify datasets
    ''' </summary>
    ''' <param name="sMDX"></param>
    ''' <param name="checksum"></param>
    ''' <param name="cellcount"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteMDXwithChecksum(ByVal sMDX As String, sRequestParameters As String, ByRef checksum As String, ByRef cellcount As Integer)
        cleanMDX(sMDX)
        Dim connectionString As String = CubeCS
        Dim category As String = ""
        Dim FieldCount As Integer
        Dim sb As New StringBuilder("")

        cellcount = 0
        Using cmd As New AdomdCommand(sMDX, cnAs)
            If sRequestParameters <> "" Then
                parseParams(sMDX, cmd, sRequestParameters)
            End If
            Dim dr As AdomdDataReader = cmd.ExecuteReader
            FieldCount = dr.FieldCount - 1
            While dr.Read
                For i = 0 To FieldCount
                    cellcount += 1
                    If Not IsNothing(dr.GetValue(i)) Then sb.Append(dr.GetValue(i).ToString)
                Next
            End While
            dr.Close()
            If cellcount = 0 Then
                checksum = ""
            Else
                Dim h As New MD5CryptoServiceProvider
                checksum = BitConverter.ToString(h.ComputeHash(System.Text.Encoding.ASCII.GetBytes(sb.ToString)))
            End If


            Return
        End Using

    End Sub


    Public Function ExecuteMdxScalar(ByVal sMDX As String, Optional sRequestParameters As String = "") As Object
        Dim value As Object
        cleanMDX(sMDX)
        Dim connectionString As String = CubeCS
        Using cmd As New AdomdCommand(sMDX, cnAs)
            If sRequestParameters = "" Then
                parseParams(sMDX, cmd)
            Else
                parseParams(sMDX, cmd, sRequestParameters)
            End If

            Dim dr As AdomdDataReader = cmd.ExecuteReader
            dr.Read()
            value = dr.GetValue(0)
            dr.Close()
            Return value
        End Using
        Return Nothing
    End Function

    

    ''' <summary>
    ''' Replace Parameters with appropriate Ones
    ''' </summary>
    ''' <param name="cmd"></param>
    ''' <remarks></remarks>
    Private Sub replaceDefaultParams(cmd As AdomdCommand, Optional Lag As Integer = 0)
        Dim category As String = ""
        Dim key As String = ""
        Dim value As Object = Nothing
        If parameterTable Is Nothing Then Exit Sub
        Dim mdxString As String = ""


        For Each dr As DataRow In parameterTable.Rows
            key = dr(0)
            mdxString = dr("MdxDefault")
            If mdxString <> "" Then value = dr("NewValue") Else value = dr("Value")
 
            If cmd.Parameters.Contains(key) Then
                If cmd.Parameters(key).Value = "default" Then
                    cmd.Parameters(dr(0).ToString).Value = parameterValues(key)(0)
                End If
            ElseIf cmd.Parameters(key).Value = "random" Then
                Dim Generator As System.Random = New System.Random()
                Dim rnd As Integer = Generator.Next(0, parameterValues(key).Count - 1)
                cmd.Parameters(key).Value = parameterValues(key)(rnd)
            Else
                cmd.Parameters(key).Value = value
            End If
        Next
    End Sub

    ''' <summary>
    ''' Execute MDX and Return a DataTable for results
    ''' </summary>
    ''' <param name="sMDX"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteMdxDataTable(ByVal sMDX As String) As DataTable
        cleanMDX(sMDX)
        Dim connectionString As String = CubeCS
        Using cmd As New AdomdCommand(sMDX, cnAs)
            parseParams(sMDX, cmd)
            Using da As New AdomdDataAdapter(cmd)
                Using ds As New DataSet("adomd")
                    da.Fill(ds)
                    Return ds.Tables(0)
                End Using
            End Using
        End Using

        Return Nothing
    End Function


    ''' <summary>
    ''' Execute MDX and return nothing
    ''' </summary>
    ''' <param name="sMDX"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteMDX(ByVal sMDX As String)
        Call ExecuteMDX(sMDX, "")
    End Sub


    ''' <summary>
    ''' Execute MDX and return nothing
    ''' </summary>
    ''' <param name="sMDX">MDX</param>
    ''' <param name="sRequestParameters">XML Request Parameters</param>
    ''' <remarks></remarks>
    Public Sub ExecuteMDX(ByVal sMDX As String, sRequestParameters As String)
        Try
            cleanMDX(sMDX)
            Dim connectionString As String = CubeCS
            Dim category As String = ""

            'For Lag = 0 to 
            Using cmd As New AdomdCommand(sMDX, cnAs)
                If sRequestParameters <> "" Then
                    parseParams(sMDX, cmd, sRequestParameters)
                End If
                cmd.ExecuteNonQuery()
                Return
            End Using

        Catch ex As Exception
            errLastMdxQuery = sMDX
            errLastParameters = sRequestParameters
            Throw 
        End Try
        Return
    End Sub


    Public Shared Sub ExecuteXMLA(ByVal xmla As String)
        Using cnAS As New AdomdConnection(CubeCS)
            cnAS.Open()
            Using cmd As New AdomdCommand(xmla, cnAS)
                cmd.ExecuteNonQuery()
            End Using
        End Using

    End Sub


    Public Sub TestConnection()
        Dim mdx As String
        mdx = String.Format("select {0} on COLUMNS from [{1}]", "{}", My.Settings.mdCubeName)
        ExecuteMDX(mdx)
    End Sub

    Protected Overrides Sub Finalize()
        DisConnect()
        MyBase.Finalize()

    End Sub

    Public Sub LoadDefaults()
        parameterTable = SqlHelper.ExecuteDataTable("Select * from parameters where mdxDefault is not null")
        parameterTable.Columns.Add(New DataColumn("NewValue"))

        For Each dr As DataRow In parameterTable.Rows
            Dim key As String = dr("Parameter")
            Dim mdxDefault As String = dr("mdxDefault")
            Dim Category As String = dr("Category").ToString
            Dim Lag As String = dr("CacheLag")
            Dim value As String = dr("Value").ToString
            Dim FieldCount As Integer
            If mdxDefault.Length > 0 Then
                If value = "default" Then
                    dr("NewValue") = ExecuteMdxScalar(mdxDefault)
                    Dim l As New List(Of Object)
                    l.Add(dr("NewValue"))
                    parameterValues.Add(key, l)
                ElseIf value = "random" Then
                    Dim dt As DataTable = ExecuteMdxDataTable(mdxDefault)
                    FieldCount = dt.Columns.Count - 1
                    Dim l As New List(Of Object)
                    For Each item In dt.Rows
                        l.Add(item(FieldCount))
                    Next
                    parameterValues.Add(key, l)
                End If
            End If
        Next
    End Sub

End Class
